********************************************************************************
* FTX lending data
*
* Note: there are 2 files, both created from the same jupyter API calls
* but beginning in November 2022 the API started returning daily values instead of hourly
* so use the hourly values up through 9/7, then use the daily values after
********************************************************************************

import delimited "build/output/lending_rates_ftx_eastern.csv", delimiter(comma) varnames(1) stripquote(no) clear 

rename apr margin_apr
gen double eventtime = clock(eastern_datetime, "YMDhms")
format eventtime %tc
gen date = dofc(eventtime)
format date %td

// 2021-08-10 06:00:00 for USDT has a data error
replace margin_apr = . if eventtime == 1944180000000 & coin == "USDT"
replace rate = . if eventtime == 1944180000000 & coin == "USDT"

collapse (mean) margin_apr size, by(date coin) 

gen exchange = "FTX"

tempfile ftx_margin_rates
save `ftx_margin_rates'

********************************************************************************
* Bitfinex lending data
********************************************************************************
import delimited "build/output/lending_rates_bitfinex_eastern.csv", delimiter(comma) varnames(1) stripquote(no) clear 
rename annualized_apr margin_apr
gen double eventtime = clock(eastern_datetime, "YMDhms")
format eventtime %tc
gen date = dofc(eventtime)
format date %td

rename funding_amount_used size
rename ticker coin


collapse (mean) margin_apr size avg_period, by(date coin)

gen exchange = "Bitfinex"

tempfile bitfinex_margin_rates
save `bitfinex_margin_rates'

********************************************************************************
* Append together
********************************************************************************

append using `ftx_margin_rates'
sort exchange date coin

********************************************************************************
* reshape
********************************************************************************
save "build/output/lending_rates_long.dta", replace

keep date coin exchange margin_apr size
reshape wide margin_apr size, i(date coin) j(exchange) string

save "build/output/lending_rates_wide.dta", replace


